<%@ page import="custom.*, java.util.*, java.sql.*, javax.sql.*, javax.naming.*"%>
<html>
<head>
<title>Specialization Analytics</title>
</head>
<body>
<h2>Specialization Analytics</h2> <p>
<%	InitialContext cxt = new InitialContext();
	DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/postgres");
	Connection conn = ds.getConnection();
	PreparedStatement stmt = conn.prepareStatement("SELECT S.id AS specialization_id, specialization, COUNT(A.id) AS num_applicants FROM specializations AS S LEFT JOIN applicants AS A ON S.id = A.specialization_id GROUP BY S.id, S.specialization ORDER BY S.id");
	ResultSet rs = stmt.executeQuery();
%>
	<table>
	<tr> <td><h4>Specialization</h4></td> <td><h4>Number of applicants</h4></td> </tr>
<% 	while( rs.next() ) {
%>
		<tr><td><%=rs.getString("specialization") %> </td> <td><a href="applications.jsp?specialization_id=<%=rs.getInt("specialization_id") %>" ><%=rs.getInt("num_applicants") %></a> </td></tr>
	
<%	} %>
	</table>
<% 	rs.close();
	stmt.close();
	conn.close();
%>

</body>
</html>